Multi-Table Delete

Multi-Table Delete#

We know how to delete data from a single table, however, if you are confronted with a situation where you want to delete data from one table and also any related data from other tables, you can employ the multi-table delete queries. An example scenario can be that when we delete an actor from our Actors table we also want all rows in the DigitalAssets table belonging to the deleted actor removed too.

Older Syntax#

DELETE T1, T2

FROM T1, T2, T3

WHERE <condition>

Newer Syntax#

Use the newer syntax as it reads better:

DELETE FROM T1, T2

USING T1, T2, T3

WHERE <condition>

Connect to the terminal below by clicking in the widget. Once connected, the command line prompt will show up. Enter or copy and paste the command ./DataJek/Lessons/36lesson.sh and wait for the MySQL prompt to start-up.

1. Imagine we want to delete actors who have a Twitter account. At the same time, we also want to remove their Twitter account information from our DigitalAssets table. We can delete intended rows from both tables as follows:

   DELETE Actors, DigitalAssets   -- Mention tables to delete rows from
   FROM Actors   -- The inner join creates a derived table 
              -- with matching rows from both tables    
   INNER JOIN DigitalAssets
   ON Actors.Id = DigitalAssets.ActorId
   WHERE AssetType = "Twitter";
   

You can observe from the output that rows from both tables get deleted. One way to think about the query is to realize that all the rows that are returned by the inner join of the two tables based on the joining criteria and the where condition are deleted from both the tables.

The alternative and newer syntax appears below:

DELETE FROM Actors, DigitalAssets
USING Actors        
INNER JOIN DigitalAssets
ON Actors.Id = DigitalAssets.ActorId
WHERE AssetType = "Twitter";

2. Consider the query below which attempts to delete only the rows from the Actor table with Twitter accounts but fails:

   DELETE Actors 
   FROM Actors 
   WHERE EXISTS ( SELECT * 
               FROM Actors 
               INNER JOIN DigitalAssets
               ON  Id = ActorId 
               WHERE AssetType="Twitter");
   

The above query fails because MySQL disallows rows to be deleted from a table if the same table also appears in the SELECT clause, i.e., we can’t delete from a table that’s read from in a nested subquery. In this case, the Actors table also appears in the inner query’s SELECT clause. The same query is rewritten as a correlated query works:

DELETE Actors 
FROM Actors 
WHERE EXISTS (SELECT * 
              FROM DigitalAssets 
              WHERE ActorId = Id AND AssetType = "Twitter");

3. As another example, say we want to remove Johnny Depp from the Actors table and all his accounts except for his Pinterest from the DigitalAssets table at the same time. We can write a multi-table delete statement as follows:

   DELETE Actors, DigitalAssets   -- specify the tables to delete from
   
   FROM Actors, DigitalAssets   -- reference tables
   
   WHERE ActorId = Id   -- conditions to narrow down rows         
   AND FirstName = "Johnny"
   AND AssetType != "Pinterest";
   

4. ORDER BY and LIMIT clauses can’t be used with multi-table deletes.